import mysql.connector
import json
from datetime import datetime, date
from tanxi.client.Handler.Logger import log_error

class DatabaseHandler:
    def __init__(self, **config):
        self.config = config
        self.connection = None
        self.cursor = None

    def connect(self):
        """连接到 MySQL 数据库"""
        try:
            self.connection = mysql.connector.connect(**self.config,buffered=True)
            self.cursor = self.connection.cursor(dictionary=True)  # 使用字典游标
            return self.connection
        
        except mysql.connector.Error as err:
            log_error(f"数据库连接失败: {err}")

    def execute_query(self, query, params=None):
        """执行SQL查询"""
        try:
            self.cursor.execute(query, params or ())
            if query.strip().lower().startswith(('select', 'show')):  # 如果是查询操作
                return self.cursor.fetchall()  # 返回查询结果
            else:  # 如果是插入、更新或删除操作
                self.connection.commit()  # 提交事务
                return self.cursor.rowcount  # 返回受影响的行数
        except mysql.connector.Error as err:
            log_error(f"执行查询失败: {err}")
            return None

    def _clear_unread_results(self):
        """清理未读取的结果集"""
        try:
            while self.cursor.nextset():  # 清理所有未读取的结果集
                pass
        except mysql.connector.errors.InterfaceError:
            pass  # 如果没有剩余结果，忽略错误

    def close(self):
        """关闭数据库连接"""
        if self.cursor:
            # 确保所有结果都已读取
            self._clear_unread_results()
            self.cursor.close()
        if self.connection:
            self.connection.close()

    def datetime_serializer(self, obj):
        """自定义序列化函数，处理 datetime 和 date 对象"""
        if isinstance(obj, (datetime, date)):
            return obj.isoformat()  # 将 datetime/date 转换为 ISO 格式字符串
        raise TypeError(f"Type {type(obj)} not serializable")

    def query_to_json(self, query):
        """执行查询并将结果转换为 JSON 格式"""
        results = self.execute_query(query)
        if results:
            return json.dumps(results, ensure_ascii=False, indent=4, default=self.datetime_serializer)
        else:
            return json.dumps({"error": "未获取到查询结果。"}, ensure_ascii=False, indent=4)